using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace Portal.Core.DAL
{
    public class VoteDB : Portal.Core.DAL.MainDB_Base
    {
        /// <summary>
		/// Initializes a new instance of the <see cref="MainDB"/> class.
		/// </summary>
        public VoteDB()
		{
			// EMPTY
		}

		/// <summary>
		/// Execute Normal Query
		/// </summary>
		/// <param name="sql">Query String</param>
		/// <returns>DataTable contains query result</returns>
		public DataTable SelectQuery(string sql)
		{
			IDbCommand cmd = this.CreateCommand(sql, false);
			return this.CreateDataTable(cmd);
		}

		public object SelectScalar(string sql)
		{
			IDbCommand cmd = this.CreateCommand(sql, false);
			cmd.CommandText = sql;
			cmd.CommandType = CommandType.Text;
			return cmd.ExecuteScalar();
		}

		/// <summary>
		/// Execute Normal Nonquery
		/// </summary>
		/// <param name="sql">Query String</param>
		public void AnotherNonQuery(string sql)
		{
			IDbCommand cmd = this.CreateCommand(sql, false);
			cmd.ExecuteNonQuery();
		}

		public bool UpdateQuery(string sql, object[] parValues, string[] parNames)
		{
			IDbCommand cmd = this.CreateCommand(sql, false);
			cmd.CommandText = sql;
			for (int i = 0; i < parValues.Length; i++)
			{
				cmd.Parameters.Add(new SqlParameter(parNames.GetValue(i).ToString(), parValues.GetValue(i)));
			}
			return cmd.ExecuteNonQuery() == 1;
		}

		/// <summary>
		/// Execute a stored procedure in SQL Server
		/// </summary>
		/// <param name="nameOfStored">SP Name</param>
		/// <param name="returnDataTable">True: Return Result in a DataTable, False: SP has no Result</param>
		/// <returns>A DataTable if returnDataTable = true, or null if returnDataTable = false</returns>
		public DataTable CallStoredProcedure(string nameOfStored, bool returnDataTable)
		{
			IDbCommand cmd = this.CreateCommand(nameOfStored, true);
			if (returnDataTable)
			{
				return this.CreateDataTable(cmd);
			}
			else
			{
				cmd.ExecuteNonQuery();
				return null;
			}
		}

		/// <summary>
		/// Execute a stored procedure in SQL Server
		/// This SP require some parametters values
		/// </summary>
		/// <param name="listOfPara">An ArrayList contains Parametters instances</param>
		/// <param name="nameOfStored">SP Name</param>
		/// <param name="returnDataTable">True: Return Result in a DataTable, False: SP has no Result</param>
		/// <returns>A DataTable if returnDataTable = true, or null if returnDataTable = false</returns>
		public DataTable CallStoredProcedure(ArrayList listOfPara, string nameOfStored, bool returnDataTable)
		{
			IDbCommand cmd = this.CreateCommand(nameOfStored, true);
			for (int i = 0; i < listOfPara.Count; i++)
			{
				SqlParameter sp = (SqlParameter)listOfPara[i];
				AddParameter(cmd, sp.ParameterName, sp.DbType, sp.Value);
			}
			if (returnDataTable)
			{
				return this.CreateDataTable(cmd);
			}
			else
			{
				cmd.ExecuteNonQuery();
				return null;
			}
		}

		/// <summary>
		/// Creates a new connection to the database.
		/// </summary>
		/// <returns>An <see cref="System.Data.IDbConnection"/> object.</returns>
		protected override IDbConnection CreateConnection()
		{
            return new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["VoteDBConnectionString"]);
		}


		/// <summary>
		/// Creates a DataTable object for the specified command.
		/// </summary>
		/// <param name="command">The <see cref="System.Data.IDbCommand"/> object.</param>
		/// <returns>A reference to the <see cref="System.Data.DataTable"/> object.</returns>
		protected internal DataTable CreateDataTable(IDbCommand command)
		{
			DataTable dataTable = new DataTable();
			new System.Data.SqlClient.SqlDataAdapter((System.Data.SqlClient.SqlCommand)command).Fill(dataTable);
			return dataTable;
		}

		/// <summary>
		/// Returns a SQL statement parameter name that is specific for the data provider.
		/// For example it returns ? for OleDb provider, or @paramName for MS SQL provider.
		/// </summary>
		/// <param name="paramName">The data provider neutral SQL parameter name.</param>
		/// <returns>The SQL statement parameter name.</returns>
		protected internal override string CreateSqlParameterName(string paramName)
		{
			return "@" + paramName;
		}

		/// <summary>
		/// Creates a .Net data provider specific parameter name that is used to
		/// create a parameter object and add it to the parameter collection of
		/// <see cref="System.Data.IDbCommand"/>.
		/// </summary>
		/// <param name="baseParamName">The base name of the parameter.</param>
		/// <returns>The full data provider specific parameter name.</returns>
		protected override string CreateCollectionParameterName(string baseParamName)
		{
			return "@" + baseParamName;
		}
    }
}
